package dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

import dao.OrderDao;
import domain.Order;
import domain.User;
import util.DataSourceUtils;

public class OrderDaoImpl implements OrderDao {

	@Override
	public List<Order> findAllOrder(int page, int limit) throws SQLException {
		String sql = "select orders.*,user.* from orders,user where user.id=orders.user_id order by orders.ordertime desc limit ?,?";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		return runner.query(sql, new ResultSetHandler<List<Order>> () {

			@Override
			public List<Order> handle(ResultSet rs) throws SQLException {
				List<Order> orders = new ArrayList<Order>();
				
				while (rs.next()) {
					Order order = new Order();
					order.setId(rs.getString("orders.id"));
					order.setMoney(rs.getDouble("orders.money"));
					order.setOrdertime(rs.getDate("orders.ordertime"));
					order.setPaystate(rs.getInt("orders.payState"));
					order.setReceiverAddress(rs.getString("orders.receiverAddress"));
					order.setReceiverName(rs.getString("orders.receiverName"));
					order.setReceiverPhone(rs.getString("orders.receiverPhone"));
					
					User user = new User();
					user.setId(rs.getInt("user.id"));
					user.setEmail(rs.getString("user.email"));
					user.setGender(rs.getString("user.gender"));
					user.setIntroduce(rs.getString("user.introduce"));
					user.setPassword(rs.getString("user.PASSWORD"));
					user.setRegistTime(rs.getDate("user.registTime"));
					user.setRole(rs.getString("user.role"));
					user.setTelephone(rs.getString("user.telephone"));
					user.setUsername(rs.getString("user.username"));
					
					order.setUser(user);
					
					orders.add(order);
				}
				
				return orders;
			}
			
		}, (page - 1) * limit, limit);
	}
	
	@Override
	public boolean deleteOrderById(String id) {
		// TODO Auto-generated method stub
		String sql = "delete from orders where id=?";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		try {
			runner.update(sql, id);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public List<Order> findOrder(String id, String name, int page, int limit) throws SQLException {
		String sql = "select orders.*,user.* from orders,user where user.id=orders.user_id and orders.id=? and orders.receiverName=? order by orders.user_id limit ?,?";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		return runner.query(sql, new ResultSetHandler<List<Order>> () {

			@Override
			public List<Order> handle(ResultSet rs) throws SQLException {
				List<Order> orders = new ArrayList<Order>();
				
				while (rs.next()) {
					Order order = new Order();
					order.setId(rs.getString("orders.id"));
					order.setMoney(rs.getDouble("orders.money"));
					order.setOrdertime(rs.getDate("orders.ordertime"));
					order.setPaystate(rs.getInt("orders.payState"));
					order.setReceiverAddress(rs.getString("orders.receiverAddress"));
					order.setReceiverName(rs.getString("orders.receiverName"));
					order.setReceiverPhone(rs.getString("orders.receiverPhone"));
					
					User user = new User();
					user.setId(rs.getInt("user.id"));
					user.setEmail(rs.getString("user.email"));
					user.setGender(rs.getString("user.gender"));
					user.setIntroduce(rs.getString("user.introduce"));
					user.setPassword(rs.getString("user.PASSWORD"));
					user.setRegistTime(rs.getDate("user.registTime"));
					user.setRole(rs.getString("user.role"));
					user.setTelephone(rs.getString("user.telephone"));
					user.setUsername(rs.getString("user.username"));
					
					order.setUser(user);
					
					orders.add(order);
				}
				
				return orders;
			}
			
		}, id, name, (page - 1) * limit, limit);
	}

	
	@Override
	public Order findOrderById(String id) throws SQLException {
		String sql = "select orders.*,user.* from orders inner join user on orders.user_id=user.id where orders.id=?;";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		return runner.query(sql, new ResultSetHandler<Order> () {

			@Override
			public Order handle(ResultSet rs) throws SQLException {
				Order order = new Order();
				User user = new User();
				
				while (rs.next()) {
					user.setUsername(rs.getString("user.username"));
					user.setTelephone(rs.getString("user.telephone"));
					
					order.setId(rs.getString("orders.id"));
					order.setMoney(rs.getDouble("orders.money"));
					order.setOrdertime(rs.getDate("orders.ordertime"));
					order.setPaystate(rs.getInt("orders.payState"));
					order.setReceiverAddress(rs.getString("orders.receiverAddress"));
					order.setReceiverName(rs.getString("orders.receiverName"));
					order.setReceiverPhone(rs.getString("orders.receiverPhone"));
					order.setUser(user);
				}
				
				return order;
			}
			
		}, id);
	}

	@Override
	public boolean createOrder(Order order) throws SQLException {
		String sql = "insert into orders(id,money,receiverAddress,receiverName,receiverPhone,payState,ordertime,user_id) values(?,?,?,?,?,?,?,?)";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		try {
			runner.update(sql, order.getId(), order.getMoney(), order.getReceiverAddress(), order.getReceiverName(), order.getReceiverPhone(), order.getPaystate(), order.getOrdertime(), order.getUser().getId());
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public List<Order> findOrderByUser(int userId, int page, int limit) throws SQLException {
		String sql = "select orders.* from orders where user_id=? order by ordertime desc limit ?,?";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		return runner.query(sql, new ResultSetHandler<List<Order>> () {

			@Override
			public List<Order> handle(ResultSet rs) throws SQLException {
				List<Order> orders = new ArrayList<Order>();
				
				while (rs.next()) {
					Order order = new Order();
					order.setId(rs.getString("orders.id"));
					order.setMoney(rs.getDouble("orders.money"));
					order.setOrdertime(rs.getDate("orders.ordertime"));
					order.setPaystate(rs.getInt("orders.payState"));
					order.setReceiverAddress(rs.getString("orders.receiverAddress"));
					order.setReceiverName(rs.getString("orders.receiverName"));
					order.setReceiverPhone(rs.getString("orders.receiverPhone"));
					
					orders.add(order);
				}
				
				return orders;
			}
			
		}, userId, (page - 1) * limit, limit);
	}
	
}
